package excelrept

import (
	"bytes"
	"database/sql"
	"fmt"
	"strconv"
	"strings"
	"text/template"

	"github.com/xuri/excelize/v2"
	"shqsoft.cn/pas3ui/base"
)

//Export 导出excel
func Export(reptName string, params map[string]string, rInfo *ReptInfo) (buff *bytes.Buffer, err error) {
	var workPath string
	if workPath, err = base.WorkDir(); err != nil {
		err = fmt.Errorf("获取执行路径出错！%s", err)
		return
	}
	filePath := workPath + "/template/" + reptName + ".xlsx"
	xlsx, err := NewXlsxHelper(filePath)
	if err != nil {
		return
	}
	conf, err := getConfig(reptName)
	if err != nil {
		return
	}
	tlen := len(conf.Tables)
	if tlen == 0 {
		return nil, fmt.Errorf("报表%s没有配置tables", reptName)
	}
	offset := 0
	prevSheet := ""
	xlsx.file.WorkBook.CalcPr.ForceFullCalc = true
	for _, t := range conf.Tables {
		if t.Sheet != prevSheet {
			offset = 0
		}
		offset, err = exportTable(&t, xlsx, params, offset, 0)
		if err != nil {
			return
		}
		prevSheet = t.Sheet
	}
	exportCells(reptName, xlsx, rInfo)
	buff, err = xlsx.file.WriteToBuffer()
	return
}

func exportTable(tConf *Table, xlsx *XlsxHelper, params map[string]string, startOffset int, ret int) (endOffset int, err error) {
	var buff bytes.Buffer
	plen := len(params)
	vals := make([]interface{}, plen)
	dbCols, err := getDBColumns(tConf.Name)
	if err != nil {
		return 0, err
	}
	buff.WriteString(" select ")
	for i, col := range dbCols {
		if strings.HasPrefix(col.DataType, "Decimal") {
			buff.WriteString("toFloat64(")
			buff.WriteString(col.Name)
			buff.WriteString(") AS ")
			buff.WriteString(col.Name)
		} else {
			buff.WriteString(col.Name)
		}
		if i < len(dbCols)-1 {
			buff.WriteString(",")
		}
	}
	buff.WriteString(" from ")
	buff.WriteString(tConf.Name)
	if plen > 0 {
		if ret > 0 {
			buff.WriteString(" where 1<>1 and ")
		} else {
			buff.WriteString(" where ")
		}
		var convF ConvertFunc
		i := 0
		for k, v := range params {
			convF = nil
			for _, col := range dbCols {
				if strings.EqualFold(col.Name, k) {
					convF = col.convertFunc
					break
				}
			}
			if convF == nil {
				return 0, fmt.Errorf("参数名%s在表%s中没有定义的字段", k, tConf.Name)
			}
			buff.WriteString(k)
			buff.WriteString("=?")
			if i < plen-1 {
				buff.WriteString(" and ")
			}
			vals[i], err = convF(v)
			if err != nil {
				return 0, err
			}
			i++
		}
		buff.WriteString(" and ")
		buff.WriteString(tConf.RowNoCol)
		buff.WriteString(">0")
	}
	buff.WriteString(" ORDER BY ")
	buff.WriteString(tConf.RowNoCol)
	rs := strings.Split(tConf.DataRange, ":")
	if len(rs) != 2 {
		err = fmt.Errorf("非法的单元格表达式%s", rs)
		return
	}
	var scol, ecol, srow, erow int
	scol, srow, err = parseAxis(rs[0])
	if err != nil {
		return
	}
	ecol, erow, err = parseAxis(rs[1])
	if err != nil {
		return
	}
	rows, err := base.CHDB.Raw(buff.String(), vals...).Rows()
	if err != nil {
		return 0, err
	}
	cols, err := base.GetColumns(rows)
	if err != nil {
		return 0, err
	}
	clen := len(cols)
	valsMap := make(map[int][]sql.NullString)
	scanArgs := make([]interface{}, clen)
	var ucol string
	colConv := make(map[string]ConvertFunc)
	for _, col := range cols {
		ucol = strings.ToUpper(col)
		for _, dbcol := range dbCols {
			if ucol == strings.ToUpper(dbcol.Name) {
				colConv[ucol] = dbcol.convertFunc
				break
			}
		}
		if _, ok := colConv[ucol]; !ok {
			err = fmt.Errorf("配置错误，表%s不存在字段%s", tConf.Name, col)
		}
	}
	ri := 0
	for rows.Next() {
		for i := 0; i < 5; i++ {
			if base.IsInSliceForInt(tConf.BlankRows, ri+srow+startOffset) {
				ri++
				blankVals := make([]sql.NullString, clen)
				valsMap[ri+srow+startOffset] = blankVals
			} else {
				break
			}
		}
		values := make([]sql.NullString, clen)
		for i := 0; i < clen; i++ {
			scanArgs[i] = &values[i]
		}
		err = rows.Scan(scanArgs...)
		if err != nil {
			return 0, err
		}
		valsMap[ri+srow+startOffset] = values
		ri++
	}
	maxRow := ri + srow + startOffset - 1
	if tConf.FormulaDerive.Rows != nil {
		for i := erow; i < maxRow; i++ {
			err = xlsx.file.InsertRows(tConf.Sheet, i, 1)
			if err != nil {
				return 0, err
			}
			for j := scol; j <= ecol; j++ {
				cName, err := excelize.ColumnNumberToName(j)
				if err != nil {
					return 0, err
				}
				paxis := cName + strconv.Itoa(i-1)
				stl, err := xlsx.file.GetCellStyle(tConf.Sheet, paxis)
				if err != nil {
					return 0, err
				}
				naxis := cName + strconv.Itoa(i)
				xlsx.file.SetCellStyle(tConf.Sheet, naxis, naxis, stl)
			}
			for _, axis := range tConf.FormulaDerive.Rows {
				col, row, err := parseAxis(axis)
				if err != nil {
					return 0, err
				}
				fm, err := xlsx.file.GetCellFormula(tConf.Sheet, axis)
				if err != nil {
					return 0, err
				}
				fm = strings.ReplaceAll(fm, strconv.Itoa(row), strconv.Itoa(i))
				cName, err := excelize.ColumnNumberToName(col)
				if err != nil {
					return 0, err
				}
				naxis := cName + strconv.Itoa(i)
				xlsx.file.SetCellFormula(tConf.Sheet, naxis, fm)
			}
		}
	}
	if tConf.FormulaDerive.Cells != nil {
		for _, axis := range tConf.FormulaDerive.Cells {
			col, row, err := parseAxis(axis)
			if err != nil {
				return 0, err
			}
			cName, err := excelize.ColumnNumberToName(col)
			if err != nil {
				return 0, err
			}
			naxis := cName + strconv.Itoa(maxRow)
			fm, err := xlsx.file.GetCellFormula(tConf.Sheet, naxis)
			if err != nil {
				return 0, err
			}
			fm = strings.ReplaceAll(fm, strconv.Itoa(row-1), strconv.Itoa(maxRow-1))
			if err != nil {
				return 0, err
			}
			xlsx.file.SetCellFormula(tConf.Sheet, naxis, fm)
		}
	}
	noCol := -1
	if tConf.RowNoCol != "" {
		colStr := ""
		for k, v := range tConf.Columns {
			if tConf.RowNoCol == v {
				colStr = k
				break
			}
		}
		if colStr != "" {
			noCol, err = excelize.ColumnNameToNumber(colStr)
			if err != nil {
				return 0, err
			}
		}

	}
	for k, v := range valsMap {
		if noCol != -1 && k == maxRow && v[noCol].String == "999999" {
			v[noCol].String = "合计"
		}
		if err := exportRow(tConf, xlsx, cols, colConv, v, k); err != nil {
			return 0, err
		}
	}
	endOffset = maxRow - erow
	rows.Close()
	return
}
func exportCells(reptName string, xlsx *XlsxHelper, rInfo *ReptInfo) (err error) {
	conf, err := getConfig(reptName)
	if err != nil {
		return err
	}
	var cellVals map[string]string
	for sheetName := range conf.Cells {
		cellVals, err = getSpecialCellVals(reptName, sheetName, rInfo)
		if cellVals == nil {
			continue
		}
		for k, v := range cellVals {
			xlsx.file.SetCellValue(sheetName, k, v)
		}
	}
	return
}

func getSpecialCellVals(reptName, sheetName string, rInfo *ReptInfo) (cellVals map[string]string, err error) {
	conf, err := getConfig(reptName)
	if err != nil {
		return nil, err
	}
	v, ok := conf.Cells[sheetName]
	if !ok {
		return
	}
	cellVals = make(map[string]string, len(conf.Cells))
	for _, item := range v {
		tpl, err := template.New("label").Parse(item.Value)
		if err != nil {
			return nil, err
		}
		buff := new(bytes.Buffer)
		err = tpl.Execute(buff, &rInfo)
		if err != nil {
			return nil, err
		}
		cellVals[item.Cell] = buff.String()
	}
	return
}

func exportRow(tConf *Table, xlsx *XlsxHelper, cols []string, colConv map[string]ConvertFunc, datas []sql.NullString, rowNo int) (err error) {
	var index int
	var conv ConvertFunc
	var cellVal interface{}
	colMap := tConf.Columns
	if len(tConf.Columns) == 0 {
		colMap = tConf.Cells
	}
	var axis string
	for k, v := range colMap {
		index = -1
		for i, col := range cols {
			if strings.EqualFold(col, v) {
				conv = colConv[strings.ToUpper(col)]
				index = i
				break
			}
		}
		if index == -1 {
			err = fmt.Errorf("表%s中没有找到字段%s", tConf.Name, v)
			return
		}
		axis = k
		if len(tConf.Columns) > 0 {
			axis = k + strconv.Itoa(rowNo)
		}
		if isUnused(tConf, axis) {
			continue
		}
		// axis := k + strconv.Itoa(rowNo)
		// if isFormula(tConf, axis) || isUnused(tConf, axis) {
		// 	continue
		// }
		// xlsx.file.WorkBook.CalcPr.ForceFullCalc = true
		if datas[index].Valid && datas[index].String != "" {
			cellVal, err = conv(datas[index].String)
			if err != nil {
				return
			}
			if tConf.Unit != 0 {
				if fval, ok := cellVal.(float64); ok {
					cellVal = fval / float64(tConf.Unit)
				}
			}
			xlsx.file.SetCellValue(tConf.Sheet, axis, cellVal)
		}
	}
	return
}

// func isFormula(tConf *Table, axis string) (is bool) {
// 	for _, rg := range tConf.FormulaCells {
// 		if is = inRange(rg, axis); is {
// 			return
// 		}
// 	}
// 	return
// }
func isUnused(tConf *Table, axis string) (is bool) {
	_, srow, _ := parseAxis(axis)
	if base.IsInSliceForInt(tConf.BlankRows, srow) {
		return true
	}
	for _, rg := range tConf.UnusedCells {
		if is = inRange(rg, axis); is {
			return
		}
	}
	return
}
func inRange(rg, axis string) (in bool) {
	rgs := strings.Split(rg, ":")
	if len(rgs) != 2 {
		return false
	}
	scol, srow, err := parseAxis(rgs[0])
	if err != nil {
		return false
	}
	ecol, erow, err := parseAxis(rgs[1])
	if err != nil {
		return false
	}
	col, row, err := parseAxis(axis)
	if err != nil {
		return false
	}
	if row >= srow && row <= erow && col >= scol && col <= ecol {
		in = true
	}
	return
}
